﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlTypes;

namespace Print_UI
{
    public partial class FrmCheckNewDataInexcel : CommonForm
    {
        public FrmCheckNewDataInexcel()
        {
            InitializeComponent();
        }

        public DataTable tbStudentCode;
        public DataTable tbClass;
        public DataTable tbSubject;
        public DataTable tbExcel, tbScore;
        public int iDepartmentID;
        public int iYearID;
        public int iSemesterID;
        public int istudent, iClass, iSubject;
        private SqlConnection _cnn;
        public SqlConnection Cnn
        {
            get { return _cnn; }
            set { _cnn = value; }
        }
        private void FrmCheckNewDataInexcel_Load(object sender, EventArgs e)
        {
            gvdataclass.DataSource = tbClass;
            gvdatastudent.DataSource = tbStudentCode;
            gvdatasubject.DataSource = tbSubject;


            var query = from obj in Model.CAT_Sector.Where(c => c.DepartmentID == iDepartmentID)
                        group obj by new { obj.DepartmentID } into countDepartment
                        select new { totalSector = countDepartment.Count(), countDepartment.Key.DepartmentID };
            if (query.FirstOrDefault().totalSector == 1)
            {

                for (int irow = 0; irow < tbClass.Rows.Count; irow++)
                {
                    var sectordefaulf = from objsector in Model.CAT_Sector.Where(c => c.DepartmentID == query.FirstOrDefault().DepartmentID)
                                        select new { objsector.ID };
                    tbClass.Rows[irow]["SectorID"] = sectordefaulf.FirstOrDefault().ID;

                }

            }
            else if (query.FirstOrDefault().totalSector > 1)
            {

                FrmCheckSetorID frm = new FrmCheckSetorID();
                frm.tbClassInSector = tbClass;
                frm.iDepartment = iDepartmentID;
                frm.ShowDialog();
            }


        }
        private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                // try opening connection into database

                if (tbStudentCode.Rows.Count == 0 || tbClass.Rows.Count == 0 || tbSubject.Rows.Count == 0)
                {
                    MessageBox.Show("Dữ liệu chưa có", "Cảnh báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                }
                else
                {
                    Cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString);
                    Cnn.Open();
                    // code import here

                    for (int iRow = 0; iRow < tbSubject.Rows.Count; iRow++)
                    {
                        if (Convert.ToBoolean(tbSubject.Rows[iRow]["Check"]) == true)
                        {
                            if (Convert.ToInt32(tbSubject.Rows[iRow]["ID"]) == -1)
                            {
                                SqlCommand cmmInsert = new SqlCommand();
                                cmmInsert.Connection = Cnn;
                                cmmInsert.CommandType = CommandType.StoredProcedure;
                                cmmInsert.CommandText = "sp_InsertSubject";
                                // To setup output parameter 
                                cmmInsert.Parameters.Add("@p_id", SqlDbType.Int).Direction = ParameterDirection.Output;
                                // To setup  input Parameters
                                cmmInsert.Parameters.Add("@p_Code", SqlDbType.NVarChar).Value = tbSubject.Rows[iRow]["Subjectcode"];
                                cmmInsert.Parameters.Add("@p_SubjectName", SqlDbType.NVarChar).Value = tbSubject.Rows[iRow]["SubjectName"];
                                cmmInsert.Parameters.Add("@p_createBy", SqlDbType.NVarChar).Value = UserName;
                                cmmInsert.Parameters.Add("@p_createDate", SqlDbType.DateTime).Value = DateTime.Now;
                                iSubject = cmmInsert.ExecuteNonQuery();
                                // To get return value when insert a new subject.
                                int iSubjectID = Convert.ToInt32(cmmInsert.Parameters["@p_id"].Value);
                                tbSubject.Rows[iRow]["ID"] = iSubjectID;
                                tbSubject.Rows[iRow]["Check"] = false;
                            }
                            else if (Convert.ToInt32(tbSubject.Rows[iRow]["ID"]) > -1)
                            {
                                SqlCommand cmmUpdate = new SqlCommand("Update PRI_Subjects set SubjectName = @SubjectName where ID=@ID", Cnn);
                                cmmUpdate.Parameters.Add("@ID", SqlDbType.Int).Value = tbSubject.Rows[iRow]["ID"];
                                cmmUpdate.Parameters.Add("@SubjectName", SqlDbType.NVarChar).Value = tbSubject.Rows[iRow]["SubjectName"];
                                cmmUpdate.ExecuteNonQuery();
                                tbSubject.Rows[iRow]["Check"] = false;
                            }

                            tbSubject.Rows[iRow]["Note"] = "Môn học đã tồn tại";

                        }
                    }

                    for (int iRow = 0; iRow < tbClass.Rows.Count; iRow++)
                    {
                        if (Convert.ToBoolean(tbClass.Rows[iRow]["Check"]) == true)
                        {

                            if (Convert.ToInt32(tbClass.Rows[iRow]["ID"]) == -1)
                            {
                                SqlCommand cmmInsert = new SqlCommand();
                                cmmInsert.Connection = Cnn;
                                cmmInsert.CommandType = CommandType.StoredProcedure;
                                cmmInsert.CommandText = "sp_InsertClass";
                                // setup output parameter 
                                cmmInsert.Parameters.Add("@p_id", SqlDbType.Int).Direction = ParameterDirection.Output;
                                // setup in input Parameters
                                cmmInsert.Parameters.Add("@p_Code", SqlDbType.NVarChar).Value = tbClass.Rows[iRow]["Class"];
                                cmmInsert.Parameters.Add("@p_DepartID", SqlDbType.Int).Value = iDepartmentID;
                                cmmInsert.Parameters.Add("@p_YearID", SqlDbType.Int).Value = iYearID;
                                cmmInsert.Parameters.Add("@p_Name", SqlDbType.NVarChar).Value = tbClass.Rows[iRow]["Class"];
                                cmmInsert.Parameters.Add("@p_SectorID", SqlDbType.Int).Value = tbClass.Rows[iRow]["SectorID"];
                                cmmInsert.Parameters.Add("@p_CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmmInsert.Parameters.Add("@p_CreateBy", SqlDbType.NVarChar).Value = UserName;
                                iClass = cmmInsert.ExecuteNonQuery();
                                int iClassID = Convert.ToInt32(cmmInsert.Parameters["@p_id"].Value);
                                tbClass.Rows[iRow]["ID"] = iClassID;
                                tbClass.Rows[iRow]["Check"] = false;

                            }
                            else if (Convert.ToInt32(tbClass.Rows[iRow]["ID"]) > -1)
                            {
                                SqlCommand cmmUpdate = new SqlCommand("Update CAT_Class Set Name = @Name , SectorID = @SectorID , ModifiedDate= @ModifiedDate where ID=@ID", Cnn);
                                cmmUpdate.Parameters.Add("@ID", SqlDbType.Int).Value = tbClass.Rows[iRow]["ID"];
                                cmmUpdate.Parameters.Add("@Name", SqlDbType.NVarChar).Value = tbClass.Rows[iRow]["Class"];
                                cmmUpdate.Parameters.Add("@SectorID", SqlDbType.Int).Value = tbClass.Rows[iRow]["SectorID"];
                                cmmUpdate.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = DateTime.Now;
                                cmmUpdate.ExecuteNonQuery();
                                tbClass.Rows[iRow]["Check"] = false;
                            }

                            tbClass.Rows[iRow]["Note"] = "Lớp học đã tồn tại";

                        }
                    }

                    string sSelect = "ClassID = '" + -1 + "' AND SectorID = '" + -1 + "'";
                    DataRow[] foundrow = tbStudentCode.Select(sSelect);
                    for (int i = 0; i < foundrow.Length; i++)
                    {
                        for (int irowClass = 0; irowClass < tbClass.Rows.Count; irowClass++)
                        {
                            if (foundrow[i]["ClassCode"] == tbClass.Rows[irowClass]["Class"])
                            {
                                foundrow[i]["ClassID"] = tbClass.Rows[irowClass]["ID"];
                                foundrow[i]["SectorID"] = tbClass.Rows[irowClass]["SectorID"];
                            }
                        }
                    }

                    string sSelectImport = "Check =1";
                    DataRow[] DrImport = tbStudentCode.Select(sSelectImport);
                    // insert into Student table
                    for (int iRow = 0; iRow < DrImport.Length; iRow++)
                    {

                        if (Convert.ToInt32(DrImport[iRow]["ID"]) == -1)
                        {
                          

                                SqlCommand cmmInsert = new SqlCommand();
                                cmmInsert.Connection = Cnn;
                                cmmInsert.CommandType = CommandType.StoredProcedure;
                                cmmInsert.CommandText = "sp_InsertStudent";
                                // setup output parameter
                                cmmInsert.Parameters.Add("@p_id", SqlDbType.Int).Direction = ParameterDirection.Output;
                                // setup input parameters.
                                cmmInsert.Parameters.Add("@p_Code", SqlDbType.NVarChar).Value = DrImport[iRow]["StudentCode"];
                                cmmInsert.Parameters.Add("@p_DepartID", SqlDbType.Int).Value = iDepartmentID;
                                cmmInsert.Parameters.Add("@p_YearID", SqlDbType.Int).Value = iYearID;
                                cmmInsert.Parameters.Add("@p_SectorID", SqlDbType.Int).Value = DrImport[iRow]["SectorID"];
                                cmmInsert.Parameters.Add("@p_ClassID", SqlDbType.Int).Value = DrImport[iRow]["ClassID"];
                                cmmInsert.Parameters.Add("@p_FName", SqlDbType.NVarChar).Value = DrImport[iRow]["Fistname"];
                                cmmInsert.Parameters.Add("@p_LName", SqlDbType.NVarChar).Value = DrImport[iRow]["LastName"];
                                cmmInsert.Parameters.Add("@p_Fee", SqlDbType.Money).Value = 0;
                                istudent = cmmInsert.ExecuteNonQuery();
                                int iStudentID = Convert.ToInt32(cmmInsert.Parameters["@p_id"].Value);
                                DrImport[iRow]["ID"] = iStudentID;
                                DrImport[iRow]["Check"] = false;

                            
                        }
                        else if (Convert.ToInt32(DrImport[iRow]["ID"]) > -1)
                        {
                            SqlCommand cmmUpdate = new SqlCommand("Update PRI_Student set Firstname = @FName,Lastname = @LName, ClassID = @ClassID,SectorID = @SectorID  where ID=@ID", Cnn);
                            cmmUpdate.Parameters.Add("@ID", SqlDbType.Int).Value = DrImport[iRow]["ID"];
                            cmmUpdate.Parameters.Add("@FName", SqlDbType.NVarChar).Value = DrImport[iRow]["Fistname"];
                            cmmUpdate.Parameters.Add("@LName", SqlDbType.NVarChar).Value = DrImport[iRow]["LastName"];
                            cmmUpdate.Parameters.Add("@ClassID", SqlDbType.Int).Value = DrImport[iRow]["ClassID"];
                            cmmUpdate.Parameters.Add("@SectorID", SqlDbType.Int).Value = DrImport[iRow]["SectorID"];
                            cmmUpdate.ExecuteNonQuery();
                            DrImport[iRow]["Check"] = false;
                        }


                        DrImport[iRow]["Note"] = "Sinh viên đã tồn tại";
                    }
                    this.Close();
                }
                // Closing connection
                Cnn.Close();

                for (int iRow = 0; iRow < tbExcel.Rows.Count; iRow++)
                {
                    string expression = "StudentCode = '" + tbExcel.Rows[iRow]["StudentCode"] + "'";
                    DataRow[] foundRowsStudent;
                    foundRowsStudent = tbStudentCode.Select(expression);
                    if (foundRowsStudent.Length == 0)
                    {
                        tbExcel.Rows[iRow]["Check"] = false;

                    }
                    else
                    {
                        int iStudentID = Convert.ToInt32(foundRowsStudent[0]["ID"]);
                        tbExcel.Rows[iRow]["IDStudent"] = iStudentID;
                        // Select query on datatable for comparing with database Class.
                        string expression1 = "Class = '" + tbExcel.Rows[iRow]["Class"] + "'";
                        DataRow[] foundRowsClass;
                        foundRowsClass = tbClass.Select(expression1);
                        if (foundRowsClass.Length == 0)
                        {
                            tbExcel.Rows[iRow]["Check"] = false;

                        }
                        else
                        {

                            int iClassID = Convert.ToInt32(foundRowsClass[0]["ID"]);
                            tbExcel.Rows[iRow]["IDClass"] = iClassID;
                            // Select query on datatable for comparing with database Class.
                            string expression2 = "Subjectcode = '" + tbExcel.Rows[iRow]["Subjectcode"] + "'";
                            DataRow[] foundRowsSbject;
                            foundRowsSbject = tbSubject.Select(expression2);
                            if (foundRowsSbject.Length == 0)
                            {
                                tbExcel.Rows[iRow]["Check"] = false;

                            }
                            else
                            {

                                int iSubjectID = Convert.ToInt32(foundRowsSbject[0]["ID"]);
                                tbExcel.Rows[iRow]["IDSubject"] = iSubjectID;
                                string expression3 = "StudentID = '" + iStudentID + "' AND ClassID = '" + Convert.ToInt32(iClassID) + "' AND SubjectID = '" + Convert.ToInt32(iSubjectID) + "' ";
                                DataRow[] foundRowsSCore;
                                foundRowsSCore = tbScore.Select(expression3);
                                if (foundRowsSCore.Length != 0)
                                {
                                    tbExcel.Rows[iRow]["Check"] = false;


                                }
                                else
                                {
                                    tbExcel.Rows[iRow]["Check"] = true;
                                    tbExcel.Rows[iRow]["Note"] = string.Empty;
                                }

                            }
                        }

                    }
                }


                MessageBox.Show("Cập nhật thành công");
                this.Close();
            }


            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);

            }
            finally
            {
                // Closing connection
                Cnn.Close();
            }

        }

        private void barButtonItem2_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("Bạn muốn thoát giao diện này?", "Thoát", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                this.Close();
            }

        }
    }
}





